package k1_jdbc.demo02.userOperation.jdbc;

import k1_jdbc.demo02.userOperation.entity.User;
import k1_jdbc.tool.ConnectionTool;
import k1_jdbc.tool.Const;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

public class UserJDBC {

    Connection conn = null;
    PreparedStatement ps = null;
    PreparedStatement ps_insertLoginInfo = null;
    PreparedStatement ps_selectLoginCount = null;
    PreparedStatement ps_selectLastLoginTime = null;
    PreparedStatement ps_updateUserLogin = null;
    ResultSet rs = null;
    ResultSet rs_selectLoginCount = null;
    ResultSet rs_selectLastLoginTime = null;


    Random random = new Random();

    static{
        try {
            Class.forName(Const.DRIVER);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 通过登录名查找用户
     * @param loginName
     * @return
     */
//    public User selectUser(String loginName){
//        return selectUser(loginName,null);
//    }

    /**
     * 通过登录名查找用户
     * @param loginName
     * @return
     */
    public User selectUser(String loginName){

        //初始化一个User类型对象
        User user = null;

        try{
            conn = DriverManager.getConnection(
                    Const.URL_TEST,
                    Const.USERNAME,
                    Const.PASSWORD
            );
            String sql = "select * from tb_user where loginName=?";
            //如果密码不为空，加密码的条件，这里的 "" 根据实际情况决定是否添加
//            if(password!=null){
//                sql += " and password=?";
//            }

            //预处理形式，与Statement的区别是创建对象的时候就必须给定一个预先处理的sql语句，然后后续向？设置内容
            ps = conn.prepareStatement(sql);

            ps.setString(1,loginName);
//            if(password!=null){
//                ps.setString(2,password);
//            }

            rs = ps.executeQuery();

            //如果返回的rs不为空，封装User
            if(rs.next()){
                user = new User(
                        rs.getString(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getInt(5),
                        rs.getString(6)
                );
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectionTool.closed(conn,ps,rs);
        }

        return user;
    }

    /**
     * 按照登录名和密码同时作为条件查询用户，如果查到，直接更新登录信息
     * 关于事务
     *      jdbc 默认自动提交事务
     * @param loginName
     * @param password
     * @return
     */
    public User selectUserAndUpdateLoginInfo(String loginName,String password){
        //初始化一个User类型对象
        User user = null;

        try{
            conn = DriverManager.getConnection(
                    Const.URL_TEST,
                    Const.USERNAME,
                    Const.PASSWORD
            );

            //设置不自动提交事务，必须根据业务逻辑手动提交或回滚事务
            conn.setAutoCommit(false);

            String sql = "select * from tb_user where loginName=? and password=?";
            String sql_insertLoginInfo = "insert into tb_login_log values(?,?,?,?)";
            String sql_selectLoginCount = "select count(*) as loginCount from tb_login_log where userno=?";
            String sql_selectLastLoginTime = "select loginTime from tb_login_log where userno=? order by loginTime desc limit 1";
            String sql_updateUserLogin = "update tb_user set loginCount=?,lastLoginTime=? where userno=?";

            //预处理形式，与Statement的区别是创建对象的时候就必须给定一个预先处理的sql语句，然后后续向？设置内容
            ps = conn.prepareStatement(sql);
            ps_insertLoginInfo = conn.prepareStatement(sql_insertLoginInfo);
            ps_selectLoginCount = conn.prepareStatement(sql_selectLoginCount);
            ps_selectLastLoginTime = conn.prepareStatement(sql_selectLastLoginTime);
            ps_updateUserLogin = conn.prepareStatement(sql_updateUserLogin);

            ps.setString(1,loginName);
            ps.setString(2,password);

            rs = ps.executeQuery();

            //如果返回的rs不为空，封装User
            if(rs.next()){

                ps_insertLoginInfo.setString(1, UUID.randomUUID().toString().toUpperCase());
                ps_insertLoginInfo.setString(2, rs.getString("userno"));
                ps_insertLoginInfo.setString(3,random.nextInt(256)+"."+random.nextInt(256)+"."+random.nextInt(256)+"."+random.nextInt(256));
                ps_insertLoginInfo.setString(4,LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));

                int insertLoginInfoCount = ps_insertLoginInfo.executeUpdate();

                if(insertLoginInfoCount==1){

                    int loginCount = 0;
                    String lastLoginTime = "";

                    //执行查询当前用户总登录次数的语句
                    ps_selectLoginCount.setString(1,rs.getString("userno"));
                    rs_selectLoginCount = ps_selectLoginCount.executeQuery();

                    if (rs_selectLoginCount.next()) {
                       loginCount = rs_selectLoginCount.getInt("loginCount");
                    }

                    //执行查询当前用户最后一次登录时间的语句
                    ps_selectLastLoginTime.setString(1,rs.getString("userno"));
                    rs_selectLastLoginTime = ps_selectLastLoginTime.executeQuery();

                    if(rs_selectLastLoginTime.next()){
                        lastLoginTime = rs_selectLastLoginTime.getString("loginTime");
                    }

                    //封装User对象
                    user = new User(
                            rs.getString(1),
                            rs.getString(2),
                            rs.getString(3),
                            rs.getString(4),
                            loginCount,
                            lastLoginTime
                    );

                    ps_updateUserLogin.setInt(1,loginCount);
                    ps_updateUserLogin.setString(2,lastLoginTime);
                    ps_updateUserLogin.setString(3,rs.getString("userno"));

                    int updateUserLoginCount = ps_updateUserLogin.executeUpdate();

                    if(updateUserLoginCount==1){
                        //如果根据用户名和密码查询到了结果并且登录日志信息也添加成功，直接提交
                        conn.commit();
                    }

                }else{
                    //如果根据用户名和密码查询到了结果但是登录日志信息添加失败，回滚
                    conn.rollback();
                }
            }


        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectionTool.closed(conn,ps,rs);
        }

        return user;
    }

    /**
     * 向表中添加多个User
     * @param users
     * @return
     */
    public boolean addUser(List<User> users){

        boolean result = true;

        try{
            Connection conn = DriverManager.getConnection(
                    Const.URL_TEST,
                    Const.USERNAME,
                    Const.PASSWORD
            );

            PreparedStatement ps = conn.prepareStatement("insert into tb_user values(?,?,?,?,?,?)");

            //遍历User列表，把每个User 都添加到批处理中
            for (User user : users) {
                ps.setString(1,user.getUserNo());
                ps.setString(2,user.getUserName());
                ps.setString(3,user.getLoginName());
                ps.setString(4,user.getPassword());
                ps.setInt(5,user.getLoginCount());
                ps.setString(6,user.getLastLoginTime());
                ps.addBatch();
            }

            //最后执行一次批处理即可
            int[] insertResultArray = ps.executeBatch();

            //如果结果的数组长度等于User列表的长度且每个元素值都是1，返回为true
            result = insertResultArray.length==users.size() && Arrays.stream(insertResultArray).allMatch(n->n==1);

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectionTool.closed(conn,ps);
        }

        return result;
    }


    /**
     * 登录时更新登录次数和时间
     * @return
     */
    public int updateLoginCountAndTime(String userno){
        int result = 0;

        try{
            Connection conn = DriverManager.getConnection(
                    Const.URL_TEST,
                    Const.USERNAME,
                    Const.PASSWORD
            );
            PreparedStatement ps = conn.prepareStatement(
                    "update tb_user set loginCount=loginCount+1,lastLoginTime=? where userno=?"
            );

            String nowTime = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));

            ps.setString(1,nowTime);
            ps.setString(2,userno);

            result = ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectionTool.closed(conn,ps);
        }

        return result;
    }

}
